package com.cater.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Excel导入导出
 * 
 * @author sd
 *
 */
public class ExcelUtil {

	/**
	 * @author
	 * @param objData   导出内容数组
	 * @param sheetName 导出工作表的名称
	 * @param columns   导出Excel的表头数组
	 * @return
	 */
	public static int exportToExcel(List<List<Object>> objData, String sheetName, List<String> columns,
			HttpServletRequest requset) {
		int flag = 0;
		// 声明工作簿jxl.write.WritableWorkbook
		WritableWorkbook wwb;
		String realPath = requset.getSession().getServletContext().getRealPath("/") + "static/upload/" + sheetName;
		try {
			// 根据传进来的file对象创建可写入的Excel工作薄
			wwb = Workbook.createWorkbook(new File(realPath));

			/*
			 * 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表
			 * 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"这样 代码中的"0"就是sheet1、其它的一一对应。
			 * createSheet(sheetName, 0)一个是工作表的名称，另一个是工作表在工作薄中的位置
			 */
			WritableSheet ws = wwb.createSheet(sheetName, 0);

			SheetSettings ss = ws.getSettings();
			ss.setVerticalFreeze(1);// 冻结表头
			WritableCellFormat wcf = getTitle();
			WritableCellFormat wcf2 = getNormolCell();
			wcf.setBackground(jxl.format.Colour.YELLOW);
			// 创建单元格样式
			// WritableCellFormat wcf = new WritableCellFormat();
			// 背景颜色
			CellView cv = new CellView();
			cv.setSize(25 * 256);
			// cv.setFormat(wcf);
			// WritableSheet ws
			/*
			 * 这个是单元格内容居中显示 还有很多很多样式
			 */

			// 判断一下表头数组是否有数据
			if (columns != null && columns.size() > 0) {

				// 循环写入表头
				for (int i = 0; i < columns.size(); i++) {

					/*
					 * 添加单元格(Cell)内容addCell() 添加Label对象Label() 数据的类型有很多种、在这里你需要什么类型就导入什么类型
					 * 如：jxl.write.DateTime 、jxl.write.Number、jxl.write.Label Label(i, 0,
					 * columns[i], wcf) 其中i为列、0为行、columns[i]为数据、wcf为样式
					 * 合起来就是说将columns[i]添加到第一行(行、列下标都是<a href=
					 * "https://www.baidu.com/s?wd=%E4%BB%8E0%E5%BC%80%E5%A7%8B&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YLPyn4uH-WmHmYnj6LPyPB0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHT4PWfkn1TvnHnkPHmsPWT3Ps"
					 * target="_blank" class="baidu-highlight">从0开始</a>)第i列、样式为什么"色"内容居中
					 */
					ws.addCell(new Label(i, 0, columns.get(i), wcf));
					ws.setColumnView(i, cv);
					// ws.setColumnView(i+1, 15);
				}
				// ws.setRowView(0, 600, false); //设置行高
				// 判断表中是否有数据
				if (objData != null && objData.size() > 0) {
					// 循环写入表中数据
					for (int i = 0; i < objData.size(); i++) {

						// 转换成map集合{activyName:测试功能,count:2}
						// Map<String, Object> map = (Map<String, Object>)objData.get(i);
						List<Object> list = objData.get(i);
						// 循环输出map中的子集：既列值
						int j = 0;
						for (Object object : list) {
							// ps：因为要“”通用”“导出功能，所以这里循环的时候不是get("Name"),而是通过map.get(o)
							ws.addCell(new Label(j, i + 1, String.valueOf(object), wcf2));
							j++;
						}
						ws.setRowView(i + 1, 300, false); // 设置行高
						/*
						 * for(Object o:map.keySet()){
						 * //ps：因为要“”通用”“导出功能，所以这里循环的时候不是get("Name"),而是通过map.get(o) ws.addCell(new
						 * Label(j,i+1,String.valueOf(map.get(o)))); j++; }
						 */
					}
				} else {
					flag = -1;
				}

				// 写入Exel工作表
				wwb.write();

				// 关闭Excel工作薄对象
				wwb.close();

			}
		} catch (IllegalStateException e) {
			System.err.println(e.getMessage());
		} catch (Exception ex) {
			flag = 0;
			ex.printStackTrace();
		}

		return flag;
	}

	public static WritableCellFormat getTitle() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 14);
		try {
			font.setColour(Colour.BLUE);// 蓝色字体
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);

		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_50);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	/**
	 * 设置其他单元格样式
	 * 
	 * @return
	 */
	public static WritableCellFormat getNormolCell() {// 12号字体,上下左右居中,带黑色边框
		WritableFont font = new WritableFont(WritableFont.TIMES, 10);
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_50);
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	public static List<List<String>> readExcel(String filePath, HttpServletRequest request) {
		List<List<String>> listplant = new ArrayList<List<String>>();
		List<String> list = new ArrayList<String>();
		list.add("档案号");
		list.add("姓名");
		list.add("性别");
		list.add("身份证");
		list.add("出生日期");
		list.add("家庭住址");
		list.add("籍贯");
		list.add("民族");
		list.add("婚姻状况");
		list.add("联系电话");
		list.add("邮箱");
		list.add("专业");
		list.add("学历");
		list.add("毕业学校");
		list.add("毕业时间");
		list.add("求职意向");
		list.add("备注");
		String realPath = request.getSession().getServletContext().getRealPath("/") + "static/upload/" + filePath;
		// DateTime tem=new DateTime("");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			InputStream is = new FileInputStream(realPath);
			Workbook rwb = Workbook.getWorkbook(is);
			// Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，二为下标，从0开始
			Sheet st = rwb.getSheet(filePath);
			int rs = st.getColumns();
			int rows = st.getRows();
			System.out.println("列数===>" + rs + "行数：" + rows);
			for (int k = 0; k < rows; k++) {// 行
				List<String> lists = new ArrayList<String>();
				for (int i = 0; i < rs; i++) {// 列
					Cell c00 = st.getCell(i, k);
					// 通用的获取cell值的方式,返回字符串
					String strc00 = c00.getContents();
					// 获得cell具体类型值的方式
					if (c00.getType() == CellType.LABEL) {
						LabelCell labelc00 = (LabelCell) c00;
						strc00 = labelc00.getString();
					}
					// excel 类型为时间类型处理;
					if (c00.getType() == CellType.DATE) {
						DateCell dc = (DateCell) c00;
						strc00 = sdf.format(dc.getDate());
					}
					// 输出
					// System.out.println(">"+strc00+"\t>"+list.get(i));
					if (k == 0) {
						if (!strc00.equals(list.get(i))) {
							return null;
						}
					} else {
						lists.add(strc00);
					}
				}
				if (lists.size() != 0) {
					listplant.add(lists);
				}

			}
			// 关闭
			rwb.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return listplant;
	}

	public static List<List<String>> readExcels(String filePath, HttpServletRequest request) {
		List<List<String>> listplant = new ArrayList<List<String>>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			InputStream is = new FileInputStream(filePath);
			Workbook rwb = (Workbook) WorkbookFactory.create(is);
			// Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，二为下标，从0开始
			Sheet st = rwb.getSheet(0);
			int rs = st.getColumns();
			int rows = st.getRows();
			System.out.println("列数===>" + rs + "行数：" + rows);
			for (int k = 0; k < rows; k++) {// 行
				List<String> lists = new ArrayList<String>();
				for (int i = 0; i < rs; i++) {// 列
					Cell c00 = st.getCell(i, k);
					// 通用的获取cell值的方式,返回字符串
					String strc00 = c00.getContents();
					// 获得cell具体类型值的方式
					if (c00.getType() == CellType.LABEL) {
						LabelCell labelc00 = (LabelCell) c00;
						strc00 = labelc00.getString();
					}
					// excel 类型为时间类型处理;
					if (c00.getType() == CellType.DATE) {
						DateCell dc = (DateCell) c00;
						strc00 = sdf.format(dc.getDate());
					}
					// 输出

					lists.add(strc00);
				}
				if (lists.size() != 0) {
					listplant.add(lists);
				}

			}
			// 关闭
			rwb.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return listplant;
	}

	public static List<List<Object>> importExcel(File file) throws IOException {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
		if ("xls".equals(extension)) {
			return read2003Excel(file);
		} else if ("xlsx".equals(extension)) {
			return read2007Excel(file);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static List<List<Object>> read2003Excel(File file) throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		Object value = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				if (row.getPhysicalNumberOfCells() != 0) {
					cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
					DecimalFormat nf = new DecimalFormat("0");// 格式化数字
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						// System.out.println(i+"行"+j+" 列 is String type");
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						// System.out.println(i+"行"+j+" 列 is Number type ;
						// DateFormt:"+cell.getCellStyle().getDataFormatString());
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							value = nf.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						// System.out.println(i+"行"+j+" 列 is Boolean type");
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						// System.out.println(i+"行"+j+" 列 is Blank type");
						value = "";
						break;
					default:
						// System.out.println(i+"行"+j+" 列 is default type");
						value = cell.toString();
					}
					if (value == null || "".equals(value)) {
						continue;
					}
					linked.add(value);
				}
				list.add(linked);
			}
		}
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 */
	private static List<List<Object>> read2007Excel(File file) throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				if (row.getPhysicalNumberOfCells() != 0) {

					cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
					DecimalFormat nf = new DecimalFormat("0");// 格式化数字
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						// System.out.println(i+"行"+j+" 列 is String type");
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						// System.out.println(i+"行"+j+" 列 is Number type ;
						// DateFormt:"+cell.getCellStyle().getDataFormatString());
						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
							value = nf.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						// System.out.println(i+"行"+j+" 列 is Boolean type");
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
//         System.out.println(i+"行"+j+" 列 is Blank type");    
						value = "";
						break;
					default:
						// System.out.println(i+"行"+j+" 列 is default type");
						value = cell.toString();
					}
					if (value == null || "".equals(value)) {
						continue;
					}
					linked.add(value);
				}
				list.add(linked);
			}
		}
		return list;
	}
}
